﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;
using Dapper;
using Entity;
using IRepository;

namespace Repository
{
    public class PurchaseOrderRepository:IPurchaseOrderRepository
    {
        // 注入数据库连接（与SupplierRepository一致）
        private readonly IDbConnection _dbConnection;

        public PurchaseOrderRepository(IDbConnection dbConnection)
        {
            _dbConnection = dbConnection;
        }

        /// <summary>
        /// 插入主表并返回自增ID
        /// </summary>
        public async Task<int> AddPurchaseOrderAsync(PurchaseOrderEntity order, IDbTransaction transaction)
        {
            // SQL：插入wms_order_purchase_order（主表），获取自增的purchase_id
            string sql = @"
                INSERT INTO dbo.wms_order_purchase_order (
                    supplier_id, rule_id, purchase_order_no, suppliers_id, purchase_date,
                    document_status, contact_person, contact_information, purchaser, reviewer,
                     organizations_id, note, create_by, create_time, is_delete
                )
                VALUES (
                    @supplier_id, @rule_id, @purchase_order_no, @suppliers_id, @purchase_date,
                    @document_status, @contact_person, @contact_information, @purchaser, @reviewer,
                     @organizations_id, @note, @create_by, @create_time, @is_delete
                );
                SELECT CAST(SCOPE_IDENTITY() AS INT);  -- 返回刚插入的自增ID（purchase_id）
            ";

            // 执行插入并返回自增ID
            return await _dbConnection.QueryFirstAsync<int>(sql, order, transaction);
        }

        /// <summary>
        /// 批量插入明细（每条明细关联主表的purchase_id）
        /// </summary>
        public async Task<int> AddPurchaseOrderDetailsAsync(List<WmsOrderDetails> details, IDbTransaction transaction)
        {
            if (details == null || details.Count == 0)
                return 0;

            string sql = @"
                INSERT INTO dbo.wms_order_details (
                    purchase_id, Product_ID, purchase_unit_price, prod_num, purreturn_num, 
                    stkIn_num, note, create_by, create_time, is_delete
                )
                VALUES (
                    @purchase_id, @Product_ID, @purchase_unit_price, @prod_num, @purreturn_num, 
                    @stkIn_num, @note, @create_by, @create_time, @is_delete
                )
            ";

            return await _dbConnection.ExecuteAsync(sql, details, transaction);
        }

        public IDbTransaction BeginTransaction()
        {
            if (_dbConnection.State == ConnectionState.Closed)
            {
                _dbConnection.Open();
            }
            return _dbConnection.BeginTransaction();
        }

        

        public async Task<PagedViewModel<PurchaseOrderDTO>> GetPurchaseOrdersAsync(
            int currentPage, int pageSize,
            string? purchaseOrderNo, string? status,
            DateTime? startDate, DateTime? endDate)
        {
            // 1. 联表：主表 → 明细表 → 货品表，获取货品名称
            var sql = @"
                SELECT 
                    -- 主表字段
                    po.purchase_id,
                    po.purchase_order_no,
                    po.document_status,
                    po.purchase_date,
                    po.suppliers_id,
                    po.contact_person,
                    po.contact_information,
                    po.organizations_id,
                    po.purchaser,
                    po.create_by,
                    po.create_time,
                    po.reviewer,
                    po.review_time,
                    -- 明细表汇总
                    ISNULL(SUM(det.prod_num), 0) AS TotalQuantity,
                    ISNULL(SUM(det.purchase_unit_price * det.prod_num), 0) AS TotalAmount,
                    -- 拼接货品名称（关联货品表）
                    STUFF((
                        SELECT ',' + prod.product_name  
                        FROM wms_order_details det2
                        LEFT JOIN wms_basic_product_information prod 
                            ON det2.Product_ID = prod.Product_ID  -- 关联货品表主键
                        WHERE det2.purchase_id = po.purchase_id
                          AND prod.is_delete = '0'  -- 过滤已删除货品
                        FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS ProductNames
                FROM wms_order_purchase_order po
                LEFT JOIN wms_order_details det 
                    ON po.purchase_id = det.purchase_id 
                   AND det.is_delete = '0'
                WHERE po.is_delete = '0'
            ";


            var parameters = new DynamicParameters();
            // 4. 原有筛选条件（不变）
            if (!string.IsNullOrEmpty(purchaseOrderNo))
            {
                sql += " AND po.purchase_order_no LIKE @PurchaseOrderNo ";
                parameters.Add("PurchaseOrderNo", $"%{purchaseOrderNo}%");
            }
            if (!string.IsNullOrEmpty(status))
            {
                sql += " AND po.document_status = @Status ";
                parameters.Add("Status", status);
            }
            if (startDate.HasValue)
            {
                sql += " AND po.purchase_date >= @StartDate ";
                parameters.Add("StartDate", startDate.Value);
            }
            if (endDate.HasValue)
            {
                sql += " AND po.purchase_date <= @EndDate ";
                parameters.Add("EndDate", endDate.Value);
            }

            // 5. 按主表主键分组（确保每个订单只返回一条记录）
            sql += " GROUP BY po.purchase_id, po.purchase_order_no, po.document_status, po.purchase_date, " +
                   "po.suppliers_id, po.contact_person, po.contact_information, po.organizations_id, " +
                   "po.purchaser, po.create_by, po.create_time, po.reviewer, po.review_time ";
            
            // 6. 分页逻辑（不变）
            var countSql = $"SELECT COUNT(*) FROM ({sql}) AS temp";
            var total = await _dbConnection.ExecuteScalarAsync<int>(countSql, parameters);

            sql += " ORDER BY po.create_time DESC OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
            parameters.Add("Offset", (currentPage - 1) * pageSize);
            parameters.Add("PageSize", pageSize);

            // 7. 查询并返回DTO（ProductNames已包含真实货品名称）
            var items = await _dbConnection.QueryAsync<PurchaseOrderDTO>(sql, parameters);

            return new PagedViewModel<PurchaseOrderDTO>
            {
                items = items.AsList(),
                total = total
            };
        }



        /// <summary>
        /// 查询主表信息(详情)
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public async Task<PurchaseOrderMainDTO> GetMainOrderByNoAsync(string orderNo)
        {
            string sql = @"
        SELECT 
            po.purchase_id,
            po.purchase_order_no,
            po.document_status,
            po.purchase_date,
            po.suppliers_id,
            po.contact_person,
            po.contact_information,
            po.organizations_id,
            po.purchaser,
            po.create_by,
            po.create_time,
            po.reviewer,
            po.review_time,
            po.note,
            po.update_by,
            po.update_time,
            po.is_delete,
            po.review_text, -- 新增需要的列（根据实际表结构调整）
            ISNULL(SUM(det.prod_num), 0) AS TotalQuantity,
            ISNULL(SUM(det.purchase_unit_price * det.prod_num), 0) AS TotalAmount,
            s.supplier_name AS SupplierName
        FROM wms_order_purchase_order po
        LEFT JOIN wms_order_details det ON po.purchase_id = det.purchase_id AND det.is_delete = '0'
        LEFT JOIN wms_basic_supplier_management s ON po.suppliers_id = s.supplier_id
        WHERE po.purchase_order_no = @OrderNo AND po.is_delete = '0'
        GROUP BY 
            po.purchase_id, 
            po.purchase_order_no, 
            po.document_status, 
            po.purchase_date,
            po.suppliers_id, 
            po.contact_person, 
            po.contact_information, 
            po.organizations_id,
            po.purchaser, 
            po.create_by, 
            po.create_time, 
            po.reviewer, 
            po.review_time,
            po.note, 
            po.update_by, 
            po.update_time, 
            po.is_delete,
            po.review_text, -- 必须与SELECT中的非聚合列一一对应
            s.supplier_name
    ";
            return await _dbConnection.QueryFirstOrDefaultAsync<PurchaseOrderMainDTO>(sql, new { OrderNo = orderNo });
        }

        /// <summary>
        /// 查询明细列表（详情）
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public async Task<List<PurchaseOrderDetailDTO>> GetDetailsByNoAsync(string orderNo)
        {
            string sql = @"
        SELECT 
                det.purchase_details_id,  -- 明细唯一标识
                det.purchase_id,  -- 新增：关联的主表订单ID（关键）
                det.Product_ID,
                p.product_name,
                p.product_code,       
                p.specification_model, 
                d.DictLabel AS unit_name,
                t.product_type_name AS type_of_goods_name,
                det.purchase_unit_price,
                det.prod_num,
                det.stkIn_num,      
                det.purreturn_num,
                det.note,
                ISNULL(SUM(ki.current_inventory), 0) AS current_inventory  -- 库存聚合
            FROM wms_order_details det
            INNER JOIN wms_order_purchase_order po ON det.purchase_id = po.purchase_id
            INNER JOIN wms_basic_product_information p ON det.Product_ID = p.Product_ID
            LEFT JOIN wms_Dictionary_data d 
                ON p.unit = d.DictValue AND d.DictTypeCode = 'Unit'
            LEFT JOIN wms_basic_type_of_goods t 
                ON p.product_type_id = t.product_type_id
            LEFT JOIN wms_knzy_inventory ki 
                ON p.product_code = ki.product_code 
            WHERE po.purchase_order_no = @OrderNo AND det.is_delete = '0'
            GROUP BY 
                det.purchase_details_id,
                det.purchase_id,  -- 同步添加到GROUP BY（必须）
                det.Product_ID,
                p.product_name,
                p.product_code,       
                p.specification_model, 
                d.DictLabel,
                t.product_type_name,
                det.purchase_unit_price,
                det.prod_num,
                det.stkIn_num,      
                det.purreturn_num,
                det.note
    ";
            return (await _dbConnection.QueryAsync<PurchaseOrderDetailDTO>(sql, new { OrderNo = orderNo })).AsList();
        }

        public Task UpdateReview(string code,string txt,string status)
        {
            string sql = @"update wms_order_purchase_order set document_status = @status,review_text = @txt     
                           where purchase_order_no = @code";
            return _dbConnection.ExecuteAsync(sql, new {status,txt,code});
        }

        public async Task<(PurchaseOrderMainDTO main, List<PurchaseOrderDetailDTO> details)> GetOrderWithDetailsAsync(string orderNo)
        {
            // 查询主表
            var mainSql = @"
                SELECT 
                    po.purchase_id, po.purchase_order_no, po.document_status, po.purchase_date,
                    po.suppliers_id, s.supplier_name AS supplierName, po.contact_person,
                    po.contact_information, po.organizations_id, o.organization_name AS organizations_name,
                    po.purchaser, po.create_by, po.create_time, po.reviewer, po.review_time, po.note
                FROM wms_order_purchase_order po
                LEFT JOIN wms_basic_supplier_management s ON po.suppliers_id = s.supplier_id AND s.is_delete = '0'
                LEFT JOIN wms_basic_organization o ON po.organizations_id = o.organization_id
                WHERE po.purchase_order_no = @orderNo AND po.is_delete = '0'
            ";
            var main = await _dbConnection.QueryFirstOrDefaultAsync<PurchaseOrderMainDTO>(mainSql, new { orderNo });
            if (main == null) throw new KeyNotFoundException("进货单不存在");

            // 查询明细
            var detailSql = @"
                SELECT 
                    det.purchase_id, det.Product_ID, p.product_name, p.product_code,
                    p.specification_model, p.unit, p.type_of_goods, det.purchase_unit_price,
                    du.DictLabel AS unit_name, dt.DictLabel AS type_of_goods_name,
                    det.prod_num, ISNULL(det.stkIn_num, 0) AS stkIn_num, det.note
                FROM wms_order_details det
                LEFT JOIN wms_basic_product_information p ON det.product_code = p.product_code AND p.is_delete = '0'
                LEFT JOIN wms_Dictionary_data du ON p.unit = du.DictValue AND du.DictTypeCode = 'Unit'
                LEFT JOIN wms_Dictionary_data dt ON p.type_of_goods = dt.DictValue AND dt.DictTypeCode = 'GoodsType'
                INNER JOIN wms_order_purchase_order po ON det.purchase_id = po.purchase_id AND po.purchase_order_no = @orderNo
                WHERE det.is_delete = '0'
            ";
            var details = await _dbConnection.QueryAsync<PurchaseOrderDetailDTO>(detailSql, new { orderNo });

            // 计算主表汇总字段
            main.TotalQuantity = details.Sum(d => d.prod_num);
            main.TotalAmount = details.Sum(d => d.prod_num * d.purchase_unit_price);
            main.ProductNames = string.Join(",", details.Select(d => d.product_name).Distinct());

            return (main, details.ToList());
        }


        public async Task<PagedViewModel<PurchaseOrderMainDTO>> GetRelatedOrdersAsync(string inboundType, List<string> status, int? supplierId, string? orderNo, string? purchaser, string? department, string? date, int page, int size)
        {
            if (status == null || !status.Any())
            {
                status = new List<string> { "5", "8" }; // 设置默认状态
            }

            var where = new List<string> { "po.is_delete = '0'" };

            // 仅通过status筛选单据状态（此时status已确保有值：默认5,8或用户选择的值）
            where.Add("po.document_status IN @status");

            // 其他筛选条件
            if (supplierId.HasValue)
                where.Add("po.suppliers_id = @supplierId");
            if (!string.IsNullOrEmpty(orderNo))
                where.Add("po.purchase_order_no LIKE @orderNo");
            if (!string.IsNullOrEmpty(purchaser))
                where.Add("po.purchaser LIKE @purchaser");
            if (!string.IsNullOrEmpty(department))
                where.Add("po.organizations_id IN (SELECT organization_id FROM sys_org_organizations WHERE organization_name LIKE @department)");
            if (!string.IsNullOrEmpty(date))
                where.Add("CONVERT(varchar(10), po.purchase_date, 23) = @date");

            // 主查询SQL
            var sql = $@"
                SELECT 
                    po.purchase_id, po.purchase_order_no, po.document_status, ds.DictLabel AS document_status_label,
                    po.purchase_date, po.suppliers_id, s.supplier_name AS supplierName,
                    po.contact_person, po.contact_information, po.organizations_id,
                    o.organization_name AS organizations_name, po.purchaser, po.create_by,
                    po.create_time, po.reviewer, po.review_time, po.note,
                    SUM(det.prod_num) AS TotalQuantity,
                    SUM(det.prod_num * det.purchase_unit_price) AS TotalAmount,
                    STRING_AGG(p.product_name, ',') AS ProductNames
                FROM wms_order_purchase_order po
                LEFT JOIN wms_basic_supplier_management s ON po.suppliers_id = s.supplier_id AND s.is_delete = '0'
                LEFT JOIN wms_Dictionary_data ds ON po.document_status = ds.DictValue AND ds.DictTypeCode = 'StockOrderStatus'
                LEFT JOIN wms_order_details det ON po.purchase_id = det.purchase_id AND det.is_delete = '0'
                LEFT JOIN wms_basic_product_information p ON det.Product_ID = p.Product_ID AND p.is_delete = '0'
                LEFT JOIN sys_org_organizations o ON po.organizations_id = o.organization_id
                WHERE {string.Join(" AND ", where)}
                GROUP BY po.purchase_id, po.purchase_order_no, po.document_status, ds.DictLabel, po.purchase_date,
                    po.suppliers_id, s.supplier_name, po.contact_person, po.contact_information,
                    po.organizations_id, o.organization_name, po.purchaser, po.create_by,
                    po.create_time, po.reviewer, po.review_time, po.note
                ORDER BY po.purchase_date DESC
                OFFSET @skip ROWS FETCH NEXT @size ROWS ONLY
                ";

            // 总数查询SQL
            var countSql = $"SELECT COUNT(DISTINCT po.purchase_id) FROM wms_order_purchase_order po LEFT JOIN wms_basic_supplier_management s ON po.suppliers_id = s.supplier_id AND s.is_delete = '0' WHERE {string.Join(" AND ", where)}";

            // 参数处理
            var param = new
            {
                inboundType = string.IsNullOrEmpty(inboundType) ? null : inboundType,
                status, // 此时status已确保有值（默认5,8或用户选择的值）
                supplierId,
                orderNo = string.IsNullOrEmpty(orderNo) ? null : $"%{orderNo}%",
                purchaser = string.IsNullOrEmpty(purchaser) ? null : $"%{purchaser}%",
                department = string.IsNullOrEmpty(department) ? null : $"%{department}%",
                date,
                skip = (page - 1) * size,
                size
            };

            // 执行查询（保持不变）
            var items = await _dbConnection.QueryAsync<PurchaseOrderMainDTO>(sql, param);
            var total = await _dbConnection.ExecuteScalarAsync<int>(countSql, param);

            return new PagedViewModel<PurchaseOrderMainDTO> { items = items.ToList(), total = total };
        }

        public async Task<PagedViewModel<PurchaseOrderDetailDTO>> GetOrderGoodsAsync(string orderNo, string? codeOrName, string? unit, int page, int size)
        {
            var where = new List<string> { "det.is_delete = '0'", "po.purchase_order_no = @orderNo" };
            if (!string.IsNullOrEmpty(codeOrName)) where.Add("(p.product_code LIKE @codeOrName OR p.product_name LIKE @codeOrName)");
            if (!string.IsNullOrEmpty(unit)) where.Add("p.unit = @unit");

            var sql = $@"
                SELECT 
                    det.purchase_id, det.Product_ID, p.product_name, p.product_code,
                    p.specification_model, p.unit, p.type_of_goods, det.purchase_unit_price,
                    du.DictLabel AS unit_name, dt.DictLabel AS type_of_goods_name,
                    det.prod_num, ISNULL(det.stkIn_num, 0) AS stkIn_num,
                    (det.prod_num - ISNULL(det.stkIn_num, 0)) AS uninbound_quantity, det.note
                FROM wms_order_details det
                LEFT JOIN wms_basic_product_information p ON det.product_code = p.product_code AND p.is_delete = '0'
                LEFT JOIN wms_Dictionary_data du ON p.unit = du.DictValue AND du.DictTypeCode = 'Unit'
                LEFT JOIN wms_Dictionary_data dt ON p.type_of_goods = dt.DictValue AND dt.DictTypeCode = 'GoodsType'
                INNER JOIN wms_order_purchase_order po ON det.purchase_id = po.purchase_id
                WHERE {string.Join(" AND ", where)}
                ORDER BY p.product_name
                OFFSET @skip ROWS FETCH NEXT @size ROWS ONLY
            ";

            var countSql = $"SELECT COUNT(1) FROM wms_order_details det LEFT JOIN wms_basic_product_information p ON det.product_code = p.product_code AND p.is_delete = '0' INNER JOIN wms_order_purchase_order po ON det.purchase_id = po.purchase_id WHERE {string.Join(" AND ", where)}";

            var param = new
            {
                orderNo,
                codeOrName = string.IsNullOrEmpty(codeOrName) ? null : $"%{codeOrName}%",
                unit,
                skip = (page - 1) * size,
                size
            };

            var items = await _dbConnection.QueryAsync<PurchaseOrderDetailDTO>(sql, param);
            var total = await _dbConnection.ExecuteScalarAsync<int>(countSql, param);

            return new PagedViewModel<PurchaseOrderDetailDTO> { items = items.ToList(), total = total };
        }

        public async Task<PurchaseOrderEntity2> GetByOrderNoAsync(string purchaseOrderNo, IDbTransaction transaction)
        {
            var sql = "SELECT * FROM wms_order_purchase_order WHERE purchase_order_no = @PurchaseOrderNo AND is_delete = '0'";
            return await _dbConnection.QueryFirstOrDefaultAsync<PurchaseOrderEntity2>(sql, new { PurchaseOrderNo = purchaseOrderNo }, transaction);
        }

        public async Task UpdateAsync(PurchaseOrderEntity2 entity, IDbTransaction transaction)
        {
            var sql = @"UPDATE wms_order_purchase_order 
                    SET document_status = @document_status, 
                        update_time = GETDATE()
                    WHERE purchase_id = @purchase_id";
            await _dbConnection.ExecuteAsync(sql, entity, transaction);
        }

        public async Task<List<PurchaseOrderDetailEntity>> GetByPurchaseIdAsync(int purchaseId, IDbTransaction transaction)
        {
            var sql = "SELECT * FROM wms_order_details WHERE purchase_id = @PurchaseId AND is_delete = '0'";
            return (await _dbConnection.QueryAsync<PurchaseOrderDetailEntity>(
                sql,
                new { PurchaseId = purchaseId },
                transaction
            )).ToList();
        }
    }
}
